package chapter13.section7;
/*程序13-6 事务管理*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BankOperation {
	private Connection con;
	public BankOperation() throws SQLException {
		super();
		String url ="jdbc:mysql://localhost:3306/bank?user=root&password=123456";
		try {
			con = DriverManager.getConnection(url);
		} catch (SQLException e) {
			throw e;//如果不能获得连接，抛出异常
		}	
	}
	//根据指定的账户id找到对应的数据库记录，并转换成Account对象
	public Account getAccount(String id) throws SQLException{
		Statement stmt =null;
		stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(
		"select id,name,balance from tb_account where id ='"+id+"'");
		Account account=null;
		if(rs.next()){
			String aid=rs.getString("id");
			String name=rs.getString("name");
			int balance=rs.getInt("balance");
			account=new Account(aid,name,balance);
		}
		rs.close();
		stmt.close();
		return account;
	}
public void transfer(Account a,Account b,int amount)throws SQLException {
		PreparedStatement pstmt =null;
		String sql="update tb_account set balance=? where id=?";
		try {
			pstmt = con.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			//关闭自动提交模式，以下语句直到commit()前都作为一个完整的事务执行
			con.setAutoCommit(false);
			//开始处理转账，将账户a上减少amount元
			pstmt.setInt(1, a.getBalance()-amount);
			pstmt.setString(2, a.getId());
			pstmt.executeUpdate();
			//将账户b上增加amount元
			pstmt.setInt(1,b.getBalance()+amount);
			pstmt.setString(2, b.getId());
			pstmt.executeUpdate();
			con.commit();//提交上面的两次操作
		} catch (SQLException e) {
			con.rollback();//回滚操作，取消本次事务中的所有已执行SQL语句
			throw e;//继续抛出异常，告诉调用者转账没有成功
		}
	}
	public static void main(String[] args) {
	  BankOperation bank=null;
	  try{
		bank=new BankOperation();
	    Account a=bank.getAccount("51146d39-fa67-463f-a82f-984f1452ff7d");
	    Account b=bank.getAccount("79a8a5fa-6be2-42d0-ac8a-7398d5de59b4");
	    if(a!=null&&b!=null){
		  bank.transfer(a, b, 100);
		}
		}catch(SQLException e){
			e.printStackTrace();
	}
}  
}
